package monitor;
/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 *
 * @author FrostBiteX77
 */

import java.sql.*;
import java.util.Vector;
import javax.swing.table.DefaultTableModel;
public class DBManager {
    
  Connection con = null;
  Statement st = null;
  ResultSet rs = null;
  DefaultTableModel dTableModel = null;

  String m_Driver = "com.mysql.jdbc.Driver";
  String m_Url = "jdbc:mysql://localhost:3306/ptsmonitoringdb";
  
  
  public DBManager(){
      
      try {
      Class.forName(m_Driver);
    }
    catch (ClassNotFoundException ex) {
      ex.printStackTrace();
    }    
      
      
  }
  
  public void Connect(){
      
      try {
      //Create connection object
      con = DriverManager.getConnection(m_Url, "root", "password");
      //Create Statement object
      st = con.createStatement();
          
      }catch (SQLException ex) {
      ex.printStackTrace();
    }     
  }
  
  
  
  public void Disconnect(ResultSet rs){
      
      try {
        if (rs != null)
          rs.close();
        if (st != null)
          st.close();
        if (con != null)
          con.close();
      }
      catch (SQLException ex) {
        ex.printStackTrace();
      }
      
  }
  
  
  
  public void Push(String query) {    

    try {
      
      
      //Execute the query
      st.executeUpdate(query);
      
  
   }
    catch (SQLException ex) {
      ex.printStackTrace();
      
    }
    
    
    
    
  }

  
  public void PullInfo(String query, String mode){
      
      try {
      
      rs = st.executeQuery(query);
            ResultSetMetaData md = rs.getMetaData();
            int columnCount = md.getColumnCount();            
            Vector columns = new Vector(columnCount);

            //store column names
            for(int i=1; i<=columnCount; i++)
            columns.add(md.getColumnName(i));

            Vector data = new Vector();
            Vector row;
            //store row data
           
            while(rs.next())
            {
            row = new Vector(columnCount);
            for(int i=1; i<=columnCount; i++)
            {
            row.add(rs.getString(i));
            }
            if(mode.equals("Add"))
            monitor.AddTutee.tutorList.addItem(rs.getString("tutor_idNum"));
            
            else if(mode.equals("Edit"))
            monitor.ViewTutor.editList.addItem(rs.getString("idNum"));
            
            else if(mode.equals("Sort"))
            monitor.UpdateSched.tutorIdList.addItem(rs.getString("idNum"));
                
          data.add(row);
           
            }
            rs.close();
            st.close();
      }catch(SQLException ex){};
      
      
      }
  

  
  public void PullCourse(String query, String mode){
      
      try {
      
      rs = st.executeQuery(query);
            ResultSetMetaData md = rs.getMetaData();
            int columnCount = md.getColumnCount();
            Vector columns = new Vector(columnCount);

            //store column names
            for(int i=1; i<=columnCount; i++)
            columns.add(md.getColumnName(i));

            Vector data = new Vector();
            Vector row;
            //store row data
           
            while(rs.next())
            {
            row = new Vector(columnCount);
            for(int i=1; i<=columnCount; i++)
            {
            row.add(rs.getString(i));
            }
            
            if(mode.equals("Tutor")){
            monitor.AddTutor.courseList1.addItem(rs.getString("Course_courseCode"));
            monitor.AddTutor.courseList2.addItem(rs.getString("Course_courseCode"));
            monitor.AddTutor.courseList3.addItem(rs.getString("Course_courseCode"));
            
            
            }
            else if(mode.equals("Tutee"))
            monitor.AddTutee.courseList.addItem(rs.getString("Course_courseCode"));
            
          data.add(row);
           
            }
            rs.close();
            st.close();
      }catch(SQLException ex){};
      
      
      }
  
   public void PullID(String query, String mode){
      
      try {
     
       rs = st.executeQuery(query);
            ResultSetMetaData md = rs.getMetaData();
            int columnCount = md.getColumnCount();
            Vector columns = new Vector(columnCount);

            //store column names
            for(int i=1; i<=columnCount; i++)
            columns.add(md.getColumnName(i));

            Vector data = new Vector();
            Vector row;
            //store row data
           
            while(rs.next())
            {
            row = new Vector(columnCount);
            for(int i=1; i<=columnCount; i++)
            {
            row.add(rs.getString(i));
            }
            
            if(mode.equals("Tutee"))
            monitor.ViewTutee.editList.addItem(rs.getString("idNum"));
          
            else if(mode.equals("Sched"))
            monitor.ViewSched.editList.addItem(rs.getString("schedNum"));  
            
            else if(mode.equals("Sort"))
            monitor.UpdateSched.tuteeIdList.addItem(rs.getString("idNum")); 
            
            data.add(row);
          
           
            }
            rs.close();
            st.close();
      }catch(SQLException ex){};
      
      
      }
   
  public void getInfo(String query){
      
      try {
      
            rs = st.executeQuery(query);
            rs.next();

            
           
      }catch(SQLException ex){
      ex.printStackTrace();
      
  }
  }
  
  public void Pull(String query) {    

    try {
      
      
      //Execute the query
      rs = st.executeQuery(query);
      ResultSetMetaData md = rs.getMetaData();
      int columnCount = md.getColumnCount();
      Vector columns = new Vector(columnCount);

            //store column names
            for(int i=1; i<=columnCount; i++)
            columns.add(md.getColumnName(i));

            Vector data = new Vector();
            Vector row;
            //store row data
            while(rs.next())
            {
            row = new Vector(columnCount);
            for(int i=1; i<=columnCount; i++)
            {
            row.add(rs.getString(i));
            }
            data.add(row);
            }
            
            dTableModel = new DefaultTableModel(data, columns);
  
   }
    catch (SQLException ex) {
      ex.printStackTrace();
    }
  
    
    
}
  
}
